import logging
import time

from Sendemail import Email

from database.connection import Link_database
# from ydsp.database.connection import Link_database
from global_file import ZYY_SEL

logger = logging.getLogger(__name__)


def OperatingSQL(data):
    """运营数据存储"""
    con, obj = Link_database()
    try:
        statdate = int(time.mktime(time.strptime(data['statdate'], '%Y-%m-%d')))
        store_name = data['store_name']
        visitors = data['visitors']
        through_visitors = data['through_visitors']
        drill_visitors = data['drill_visitors']
        sum_turnover = data['sum_turnover']
        brush_single_number = data['brush_single_number']
        brush_single_money = data['brush_single_money']
        pay_buyers_number = data['pay_buyers_number']
        free_visitors = data['free_visitors']
        price = data['price']
        conversion_rate = data['conversion_rate']
        real_pay_buyers_number = data['real_pay_buyers_number']
        real_turnover = data['real_turnover']
        members_number = data['members_number']
        members_rate = data['members_rate']
        refund_money = data['refund_money']
        real_money = data['real_money']
        name = data['name']
    except Exception as e:
        logger.error(e)
        return False

    sql_1 = """select chief_inspector,director,promoters from self_storemanage where shop_name='{}'""".format(store_name)
    obj.execute(sql_1)
    q = obj.fetchone()

    if name not in q:
        return 2

    sql = """insert into operating 
              (statdate,store_name,visitors,through_visitors,drill_visitors,sum_turnover,brush_single_number,brush_single_money,pay_buyers_number,free_visitors,price,conversion_rate,real_pay_buyers_number,real_turnover,members_number,members_rate,refund_money,real_money) values 
              (%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % \
          (statdate, store_name, visitors, through_visitors, drill_visitors, sum_turnover, brush_single_number,
           brush_single_money, pay_buyers_number, free_visitors, price, conversion_rate, real_pay_buyers_number,
           real_turnover, members_number, members_rate, refund_money, real_money)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()

    return True


class GeneraOperatingSQL(object):
    """自运营店铺存储"""
    def __init__(self):
        self.con, self.obj = Link_database()

    def u_(self):
        """自运营总监，主管id"""
        sql = """select director,tube from depar_user where department=2"""
        self.obj.execute(sql)
        q = self.obj.fetchall()
        u_list = []

        for i in q:
            u_list.append(i[0])
            u_list.append(i[1])

        return u_list

    def storage(self, user_id, shop_name, chief_inspector, director, promoters):
        """存储"""
        u_list = self.u_()
        if user_id in u_list or user_id in ZYY_SEL:
            sql_1 = """select id from self_storemanage where shop_name='{}'""".format(shop_name)
            self.obj.execute(sql_1)
            if self.obj.fetchone():
                return False
            else:
                sql = """insert into self_storemanage (shop_name,chief_inspector,director,promoters) values ('{}','{}','{}','{}')""".format(shop_name, chief_inspector, director, promoters)
                self.obj.execute(sql)
                self.con.commit()

                return True
        else:
            return 2

    def modify(self, shop_name, chief_inspector, director, promoters):
        """分配"""
        sql = """update self_storemanage set chief_inspector='{}',director='{}',promoters='{}' where shop_name='{}'""".format(chief_inspector, director, promoters, shop_name)
        self.obj.execute(sql)
        self.con.commit()

    def delete_(self, shop_name):
        """删除"""
        sql = """delete from self_storemanage where shop_name='{}'""".format(shop_name)
        self.obj.execute(sql)
        self.con.commit()

    def __del__(self):
        self.obj.close()
        self.con.close()


def DelOperatingSQL(id):
    """删除运营数据"""
    con, obj = Link_database()

    sql = """delete from operating where id=%d""" % id
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def GeneraOperatTubeSQL(store_name, project_type, chief_inspecto, director, operator, start_time, end_time,
                        account_entry, amount_account, renewal_time, customer_info, subscription_info, contract_status,
                        store_status, email):
    """代运营项目管理"""
    con, obj = Link_database()

    sql = """insert into genera_operat_tube (store_name, project_type, chief_inspecto,director,operator,start_time,end_time,account_entry,amount_account,renewal_time,customer_info,subscription_info,contract_status,store_status,email) 
          values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" \
          % (store_name, project_type, chief_inspecto, director, operator, start_time, end_time, account_entry,
             amount_account, renewal_time, customer_info, subscription_info, contract_status, store_status, email)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def UpdateGeneraOperatTubeSQL(id, store_name, project_type, chief_inspecto, director, operator, start_time, end_time,
                              account_entry, amount_account, renewal_time, customer_info, subscription_info,
                              contract_status, store_status, email):
    """修改代运营项目管理"""
    con, obj = Link_database()

    sql = """update genera_operat_tube set store_name='%s',project_type='%s',chief_inspecto='%s',director='%s',operator='%s',start_time='%s',end_time='%s',account_entry='%s',amount_account='%s',renewal_time='%s',customer_info='%s',subscription_info='%s', contract_status='%s',store_status='%s',email='%s' where id=%d""" \
          % (store_name, project_type, chief_inspecto, director, operator, start_time, end_time, account_entry,
             amount_account, renewal_time, customer_info, subscription_info, contract_status, store_status, email, id)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def SomeCollectionSQL(project_type, operator, clearing_time, store_name, sales, brush_single_money, refund_money,
                      real_sales, investment, some, commission, is_settlement, special_note):
    """代运行提点回款"""
    con, obj = Link_database()

    sql = """insert into some_collection (project_type,operator,clearing_time,store_name,sales,brush_single_money,refund_money,real_sales,investment,some,commission,is_settlement,special_note) 
              values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" \
          % (project_type, operator, clearing_time, store_name, sales, brush_single_money, refund_money, real_sales,
             investment, some, commission, is_settlement, special_note)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def UpdateSomeCollectionSQL(id, project_type, operator, clearing_time, store_name, sales, brush_single_money,
                            refund_money, real_sales, investment, some, commission, is_settlement, special_note):
    """修改代运营提点回款"""
    con, obj = Link_database()

    sql = """update some_collection set project_type='%s',operator='%s',clearing_time='%s',store_name='%s',sales='%s',brush_single_money='%s',refund_money='%s',real_sales='%s',investment='%s',some='%s',commission='%s',is_settlement='%s',special_note='%s' where id=%d""" \
          % (project_type, operator, clearing_time, store_name, sales, brush_single_money, refund_money, real_sales,
             investment,
             some, commission, is_settlement, special_note, id)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def YunYingLogSQL(statdate, shop_name, sales, brush_money, visitors, conversion_rate, advertising, input_rate,
                  advertising_rate, old_customer_rate, purchased, collection_rate, uv_value, ztc_sales,
                  ztc_consumption, ztc_roi, ztc_purchased, ztc_collection, ztc_purchase_cost, ztc_click_cost,
                  ztc_uv_value, zz_sales, zz_consumption, zz_roi, zz_purchased, zz_collection, zz_purchase_cost,
                  zz_click_cost, zz_uv_value, content, user_id):
    """代运营日志存储"""
    con, obj = Link_database()

    sql = """insert into job_log values (0,%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d)""" \
          % (
          statdate, shop_name, sales, brush_money, visitors, conversion_rate, advertising, input_rate, advertising_rate,
          old_customer_rate, purchased, collection_rate, uv_value, ztc_sales,
          ztc_consumption, ztc_roi, ztc_purchased, ztc_collection, ztc_purchase_cost, ztc_click_cost, ztc_uv_value,
          zz_sales, zz_consumption, zz_roi, zz_purchased, zz_collection, zz_purchase_cost,
          zz_click_cost, zz_uv_value, content, user_id)

    obj.execute(sql)
    con.commit()

    data = {
        "shop_name": shop_name,
        "sales": sales,
        "brush_money": brush_money,
        "visitors": visitors,
        "conversion_rate": conversion_rate,
        "advertising": advertising,
        "input_rate": input_rate,
        "advertising_rate": advertising_rate,
        "old_customer_rate": old_customer_rate,
        "purchased": purchased,
        "collection_rate": collection_rate,
        "uv_value": uv_value,
        "ztc_sales": ztc_sales,
        "ztc_consumption": ztc_consumption,
        "ztc_roi": ztc_roi,
        "ztc_purchased": ztc_purchased,
        "ztc_collection": ztc_collection,
        "ztc_purchase_cost": ztc_purchase_cost,
        "ztc_click_cost": ztc_click_cost,
        "ztc_uv_value": ztc_uv_value,
        "zz_sales": zz_sales,
        "zz_consumption": zz_consumption,
        "zz_roi": zz_roi,
        "zz_purchased": zz_purchased,
        "zz_collection": zz_collection,
        "zz_purchase_cost": zz_purchase_cost,
        "zz_click_cost": zz_click_cost,
        "zz_uv_value": zz_uv_value,
        "content": content
    }

    sql_1 = """select email from genera_operat_tube where store_name='{}'""".format(shop_name)
    obj.execute(sql_1)
    q = obj.fetchone()
    if q:
        email = q[0]
        # 发送邮件
        Email(email, data)

    obj.close()
    con.close()


if __name__ == '__main__':
    data = {
        "statdate": "2018-11-27",
        "store_name": "御果旗舰店",
        "visitors": "70020",
        "through_visitors": "2190",
        "drill_visitors": "0",
        "sum_turnover": "23855",
        "brush_single_number": "171",
        "brush_single_money": "3589",
        "pay_buyers_number": "677",
        "free_visitors": "67825",
        "price": "40",
        "conversion_rate": "0.72%",
        "real_pay_buyers_number": "506",
        "real_turnover": "20266",
        "members_number": " ",
        "members_rate": " ",
        "refund_money": "1785",
        "real_money": "18481",
    }

    OperatingSQL(data)
